def make_database():
    '''create the database'''
    sql_file = open("sqldatabase.txt", "a")
    sql_file.write("CREATE DATABASE casefile_database")
    sql_file.close

def make_tables():
    '''create tables'''
    sql_file = open("sqldatabase.txt", "a")
    sql_file.write("CREATE TABLE justices(name CHAR(20) caseurl CHAR(60) PRIMARY KEY(name, caseurl)); \n")
    sql_file.write("CREATE TABLE case(date CHAR(20) docket CHAR(20) url CHAR(20) province CHAR(20) PRIMARY KEY(url)); \n")
    sql_file.write("CREATE TABLE civil(caseurl CHAR(20) name CHAR(20) PRIMARY KEY(caseurl)); \n")
    sql_file.write("CREATE TABLE criminal(caseurl CHAR(20) name CHAR(20) PRIMARY KEY(caseurl)); \n")
    sql_file.write("CREATE TABLE lawyers(name CHAR(20) caseurl CHAR(20) partyname CHAR(20) partyside CHAR(20) PRIMARY KEY(name, caseurl, partyname, partyside)); \n")
    sql_file.write("CREATE TABLE parties(side CHAR(20) name CHAR(20) PRIMARY KEY(side, name)); \n")
    sql_file.close()
    

def add2justices(name, caseURL):
    '''add tuple to table justices'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO justices VALUES(" + "'" + name + "', '" + caseURL + "'); \n")
    sql_file.close()
    
def add2case(date, docket, URL, province):
    '''add tuple to table case'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO case VALUES(" + "'" + date + "', '" + docket + "', '" + URL + "', '" + province + "'); \n")
    sql_file.close()    
    
def add2civil(caseURL, name):
    '''add tuple to table civil'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO civil VALUES(" + "'" + caseURL + "', '" + name + "'); \n")
    sql_file.close()        
    
def add2criminal(caseURL, name):
    '''add tuple to table criminal'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO criminal VALUES(" + "'" + caseURL + "', '" + name + "'); \n")
    sql_file.close()      
    
def add2lawyers(name, caseURL, partyName, partySide):
    '''add tuple to table lawyers'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO lawyers VALUES(" + "'" + name + "', '" + caseURL +"', '" + partyName + "', '" + partySide + "'); \n")
    sql_file.close()  
    
def add2parties(side, name):
    '''add tuple to table parties'''
    sql_file = open("sqldatabase.text", "a")
    sql_file.write("INSERT INTO parties VALUES(" + "'" + side + "', '" + name + "'); \n")
    sql_file.close()      
    
    
    